package com.gmrz.webauthn.db.dao;

import com.gmrz.uaf.common.Constants;
import com.gmrz.uaf.db.DAOException;
import com.gmrz.uaf.protocol.v1.processor.exception.UAFErrorCode;
import com.gmrz.util.Strings;
import com.gmrz.util.db.DBUtil;
import com.gmrz.webauthn.protocol.v1.processor.WebAuthnErrorCode;
import com.gmrz.webauthn.protocol.v1.schema.AAGUID;
import com.gmrz.webauthn.protocol.v1.schema.AttestationFormat;
import com.gmrz.webauthn.protocol.v1.schema.AuthenticatorTransport;
import com.gmrz.webauthn.protocol.v1.schema.WebAuthenticator;
import com.google.inject.assistedinject.Assisted;
import com.google.inject.assistedinject.AssistedInject;
import org.apache.commons.lang3.StringUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

public class BaseWebAuthnAuthenticatorDAO extends Dialect implements WebAuthnAuthenticatorsDAO {
    private static final Logger LOG = LogManager.getLogger(BaseWebAuthnAuthenticatorDAO.class);

    private static final String _SQL_SELECT_ALL = "select " +
            "a.auth_id,a.metadata_id,a.user_name,a.cust_no,a.aaid,a.keyid,a.pubkey,a.key_format,a.sign_counter,a.reg_counter," +
            "a.device_id,a.status,a.createts,a.updatets,a.tenant_id,a.trans_type,a.auth_type," +
            "w.pub_user_id,w.display_name,w.attestation_format,w.transport,w.type,w.auth_counter,w.cert_chain_hash,w.sign_algo " +
            "from t_authenticators as a,t_webauthn_authenticators as w where ";
    private static final String _SQL_UPDATE = "update t_authenticators set status=?,updatets=? where tenant_id=? and user_name=? and status=?";

    private Connection conn_;

    @AssistedInject
    public BaseWebAuthnAuthenticatorDAO(@Assisted Connection conn_) {
        this.conn_ = conn_;
    }

    @Override
    public void insert(WebAuthenticator webAuthenticator) throws DAOException {

        String q = "insert into " +
                "t_authenticators (auth_id,metadata_id,user_name,cust_no,aaid,keyid,pubkey,key_format,sign_counter,reg_counter," +
                "device_id,status,png_char,createts,updatets,tenant_id,auth_type,trans_type) " +
                "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        PreparedStatement s = null;
        ResultSet rs = null;
        try {
            int offset = 1;
            s = this.conn_.prepareStatement(q);
            s.setString(offset++, webAuthenticator.getWaId());
            s.setString(offset++, webAuthenticator.getWmId());
            s.setString(offset++, webAuthenticator.getUserName());
            s.setString(offset++, webAuthenticator.getCustNo());
            s.setString(offset++, webAuthenticator.getAaguid() != null ? webAuthenticator.getAaguid().toString() : null);
            s.setString(offset++, webAuthenticator.getCredentialId());
            s.setString(offset++, webAuthenticator.getPublicKey());
            s.setInt(offset++, webAuthenticator.getKeyFormat());
            s.setLong(offset++, webAuthenticator.getSignCounter());
            s.setInt(offset++, webAuthenticator.getRegCounter());
            s.setString(offset++, webAuthenticator.getDeviceId());
            s.setInt(offset++, Constants.AuthenticatorStatus.ACTIVE.getStatus());
            s.setString(offset++, "");

            Calendar calendar = Calendar.getInstance();
            Timestamp currentTimestamp = new Timestamp(calendar.getTime().getTime());
            s.setTimestamp(offset++, currentTimestamp);
            s.setTimestamp(offset++, currentTimestamp);
            s.setString(offset++, webAuthenticator.getTenantId());
            s.setString(offset++, webAuthenticator.getAuthType());
            s.setString(offset++, webAuthenticator.getTransType());
            s.executeUpdate();

            q = "insert into " +
                    "t_webauthn_authenticators (auth_id,pub_user_id,display_name,attestation_format,credential_id,transport,type,auth_counter,cert_chain_hash,sign_algo) " +
                    "values(?,?,?,?,?,?,?,?,?,?)";
            offset = 1;
            s = this.conn_.prepareStatement(q);
            s.setString(offset++, webAuthenticator.getWaId());
            s.setString(offset++, webAuthenticator.getUserId());
            if ((webAuthenticator.getDisplayName() != null) && (webAuthenticator.getDisplayName().equals(webAuthenticator.getUsername()))) {
                s.setString(offset++, webAuthenticator.getUsername());
            } else {
                s.setString(offset++, webAuthenticator.getDisplayName());
            }
            s.setString(offset++, webAuthenticator.getAttestationFormat().getName());
            s.setString(offset++, webAuthenticator.getCredentialId());
            List<AuthenticatorTransport> transports = webAuthenticator.getTransports();
            String transportStr = getStringFromTransportsList(transports);
            s.setString(offset++, transportStr);
            s.setString(offset++, webAuthenticator.getType());
            s.setInt(offset++, webAuthenticator.getAuthCounter());
            String ss = webAuthenticator.getAttestCertChainHash();
            s.setString(offset++, webAuthenticator.getAttestCertChainHash());
            s.setInt(offset, webAuthenticator.getSignAlgo());
            s.executeUpdate();

        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}", q);
            throw new DAOException(WebAuthnErrorCode.ERROR_DATABASE_WRITE_ERROR, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
    }

    @Override
    public List<WebAuthenticator> find(String tenantId, String hashUserName,String authType,String transType,String deviceID) throws DAOException {
        List<WebAuthenticator> authenticators = new ArrayList<WebAuthenticator>();
        String q = _SQL_SELECT_ALL + " a.user_name = ? and a.tenant_id = ? and a.status  = 1 and a.auth_id =w.auth_id and a.auth_type = ? and a.trans_type = ? ";
        if(Strings.isNotBlank(deviceID)){
            q = q + " and a.device_id = ?";
        }

        PreparedStatement s = null;
        ResultSet rs = null;

        try {
            s = this.conn_.prepareStatement(q);
            int offset = 1;
            s.setString(offset++, hashUserName);
            s.setString(offset++, tenantId);
            s.setString(offset++, authType);
            s.setString(offset++, transType);
            if(Strings.isNotBlank(deviceID)){
                s.setString(offset++, deviceID);
            }
            rs = s.executeQuery();
            while (rs.next()) {
                WebAuthenticator webAuthenticator = readAuthenticator(rs);
                authenticators.add(webAuthenticator);
            }
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}", q);
            throw new DAOException(WebAuthnErrorCode.ERROR_DATABASE_READ_ERROR, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
        return authenticators;
    }

    @Override
    public WebAuthenticator findByRawIdAndTenantId(String rawId, String tenantId) throws DAOException {
        WebAuthenticator webAuthenticator = null;
        String q = _SQL_SELECT_ALL + " w.credential_id = ? and a.tenant_id = ? and a.status  = 1 and a.auth_id =w.auth_id";

        PreparedStatement s = null;
        ResultSet rs = null;

        try {
            s = this.conn_.prepareStatement(q);
            int offset = 1;
            s.setString(offset++, rawId);
            s.setString(offset, tenantId);
            rs = s.executeQuery();
            while (rs.next()) {
                webAuthenticator = readAuthenticator(rs);
            }
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}", q);
            throw new DAOException(WebAuthnErrorCode.ERROR_DATABASE_READ_ERROR, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }
        return webAuthenticator;
    }

    private WebAuthenticator readAuthenticator(ResultSet rs) throws DAOException {
        WebAuthenticator authenticator = new WebAuthenticator();
        try {
            int offset = 1;
            authenticator.setWaId(rs.getString(offset++));
            authenticator.setWmId(rs.getString(offset++));
            String encryptedUsername = rs.getString(offset++);
            //String clearTextUsername = getDecryptedUsername(encryptedUsername, tenant.getTenantID());
            authenticator.setUsername(encryptedUsername);
            authenticator.setCustNo(rs.getString(offset++));
            String aaguid = rs.getString(offset++);
            if (!Strings.isNullOrEmpty(aaguid)) {
                authenticator.setAaguid(new AAGUID(aaguid));
            }
            authenticator.setCredentialId(rs.getString(offset++));
            authenticator.setPublicKey(rs.getString(offset++));
            authenticator.setKeyFormat(rs.getInt(offset++));
            authenticator.setSignCounter(rs.getLong(offset++));
            authenticator.setRegCounter(rs.getInt(offset++));
            //device_id
            rs.getString(offset++);
            authenticator.setStatus(rs.getInt(offset++));
            authenticator.setCreateTimeStamp(rs.getTimestamp(offset++));
            authenticator.setUpdateTimeStamp(rs.getTimestamp(offset++));
            authenticator.setTenantId(rs.getString(offset++));
            authenticator.setTransType(rs.getString(offset++));
            authenticator.setAuthType(rs.getString(offset++));
            authenticator.setUserId(rs.getString(offset++));
            authenticator.setDisplayName(rs.getString(offset++));
            authenticator.setAttestationFormat(AttestationFormat.forName(rs.getString(offset++)));
            String transportsStr = rs.getString(offset++);
            if (StringUtils.isNotBlank(transportsStr)) {
                authenticator.setTransports(getTransportsListFromString(transportsStr));
            }
            authenticator.setType(rs.getString(offset++));

            authenticator.setAuthCounter(rs.getInt(offset++));
            authenticator.setAttestCertChainHash(rs.getString(offset++));
            authenticator.setSignAlgo(rs.getInt(offset));
            LOG.debug("Authenticator : {}", authenticator);

            return authenticator;
        } catch (SQLException sqle) {
            LOG.error("Failed to read the Authenticator Data", sqle);
            throw new DAOException(WebAuthnErrorCode.ERROR_DATABASE_READ_ERROR, sqle);
        }
    }

    @Override
    public boolean updateAuthCounter(String userName, String credentialId, String tenantId, long signCounter) throws DAOException {

        String q = "update t_webauthn_authenticators set auth_counter = auth_counter + 1 where credential_id = ?";
        PreparedStatement s = null;
        int retVal;

        try {
            s = this.conn_.prepareStatement(q);
            int offset = 1;
            s.setString(offset, credentialId);
            retVal = s.executeUpdate();
            DBUtil.close(s);
            q = "update t_authenticators set sign_counter=?,updatets = ? where user_name = ? and keyid = ? and tenant_id = ? and status=1";

            s = this.conn_.prepareStatement(q);
            offset = 1;
            s.setLong(offset++, signCounter);
            Calendar calendar = Calendar.getInstance();
            Timestamp currentTimestamp = new Timestamp(calendar.getTime().getTime());
            s.setTimestamp(offset++, currentTimestamp);
            s.setString(offset++, userName);
            s.setString(offset++, credentialId);
            s.setString(offset, tenantId);
            retVal = s.executeUpdate();
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}", q);
            throw new DAOException(WebAuthnErrorCode.ERROR_DATABASE_WRITE_ERROR, sqle);
        } finally {
            DBUtil.close(s);
        }

        return retVal > 0;
    }

    @Override
    public boolean updateSignCounter(long signCounter, String userName, String credentialId, String tenantId) throws DAOException {
        String q = "update t_authenticators set sign_counter = ?,updatets = ? where user_name = ? and credential_id = ? and tenant_id = ? and status = 1";

        PreparedStatement s = null;
        ResultSet rs = null;
        int retVal;

        try {
            s = this.conn_.prepareStatement(q);
            int offset = 1;

            s.setLong(offset++, signCounter);

            Calendar calendar = Calendar.getInstance();
            Timestamp currentTimestamp = new Timestamp(calendar.getTime().getTime());
            s.setTimestamp(offset++, currentTimestamp);

            s.setString(offset++, userName);
            s.setString(offset++, credentialId);
            s.setString(offset, tenantId);
            retVal = s.executeUpdate();
        } catch (SQLException sqle) {
            LOG.error("The following SQL statement failed to execute:\n {}", q);
            throw new DAOException(WebAuthnErrorCode.ERROR_DATABASE_WRITE_ERROR, sqle);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(s);
        }

        return retVal > 0;
    }

    @Override
    public boolean delete(String appID, String hashedUsername, String keyID) throws DAOException {
        PreparedStatement s = null;
        try {
            String q = _SQL_UPDATE;
            if(Strings.isNotBlank(keyID)){
                q = q + " and keyid = ?";
            }
            s = this.conn_.prepareStatement(q);
            s.setInt(1, Constants.AuthenticatorStatus.DELETED.getStatus());
            Calendar calendar = Calendar.getInstance();
            Timestamp currentTimestamp = new Timestamp(calendar.getTime()
                    .getTime());
            s.setTimestamp(2, currentTimestamp);
            s.setString(3, appID);
            s.setString(4, hashedUsername);
            s.setInt(5, Constants.AuthenticatorStatus.ACTIVE.getStatus());
            if(Strings.isNotBlank(keyID)) {
                s.setString(6, keyID);
            }
            s.executeUpdate();
        } catch (SQLException e) {
            LOG.trace(e);
            throw new DAOException(UAFErrorCode.DB_UPDATE_AUTHENTICATOR_FAILED, e);
        } finally {
            DBUtil.close(s);
        }
        return true;
    }


    protected String getStringFromTransportsList(List<AuthenticatorTransport> transports) {
        if ((transports == null) || (transports.isEmpty())) {
            return null;
        }
        StringBuilder sb = new StringBuilder();
        for (AuthenticatorTransport t : transports) {
            sb.append(t).append(",");
        }
        sb.deleteCharAt(sb.length() - 1);
        return sb.toString();
    }

    private List<AuthenticatorTransport> getTransportsListFromString(String transportStr) throws DAOException {
        if ((transportStr == null) || (transportStr.isEmpty())) {
            return null;
        }
        List<AuthenticatorTransport> transports = new ArrayList<AuthenticatorTransport>();
        String[] transportArray = transportStr.split(",");

        for (String s : transportArray) {
            transports.add(AuthenticatorTransport.forName(s));
        }
        return transports;
    }
}
